package com.han.dao.concrete.product;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.han.bom.MultipleChoice;
import com.han.bom.Question;
import com.han.bom.TrueOrFalse;
import com.han.dao.abstraction.product.GenericQuestionDAO;

public class QuestionDAO extends DataAccessObject implements GenericQuestionDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -6268345766409733972L;

	public ArrayList<Question> retrieveAllQuestions() throws SQLException,
			ClassNotFoundException, NamingException {
		ArrayList<Question> questions = new ArrayList<Question>();
		Question question = null;
		Boolean type = null;

		try {
			this.connect();
			String select = "SELECT * FROM tbl_question";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				type = this.resultSet.getBoolean(3);

				if (type) {
					question = new MultipleChoice();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "MC");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
					questions.add(question);
				} else {
					question = new TrueOrFalse();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "TF");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
					questions.add(question);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return questions;
	}

	public Question retrieveQuestionById(String questionid)
			throws SQLException, ClassNotFoundException, NamingException {
		int qid = 0;
		Question question = null;
		Boolean type = null;

		try {
			qid = Integer.parseInt(questionid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			qid = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM tbl_question WHERE question_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, qid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				type = this.resultSet.getBoolean(3);

				if (type) {
					question = new MultipleChoice();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "MC");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
				} else {
					question = new TrueOrFalse();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "TF");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return question;
	}

	public ArrayList<Question> retrieveQuestionsbyExam(String examid)
			throws SQLException, ClassNotFoundException, NamingException {
		ArrayList<Question> questions = new ArrayList<Question>();
		int eid = 0;
		Question question = null;
		Boolean type = null;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM tbl_question AS a INNER JOIN tbl_exam_questions AS b ON a.question_id=b.question_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);

			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				type = this.resultSet.getBoolean(3);

				if (type) {
					question = new MultipleChoice();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "MC");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
					questions.add(question);
				} else {
					question = new TrueOrFalse();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "TF");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
					questions.add(question);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return questions;
	}

	public ArrayList<String> retrieveCategoriesbyExam(String examid)
			throws SQLException, ClassNotFoundException, NamingException {
		ArrayList<String> categories = new ArrayList<String>();
		int eid = 0;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT a.question_category FROM tbl_question AS a INNER JOIN tbl_exam_questions AS b ON a.question_id=b.question_id WHERE b.exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);

			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				categories.add(this.resultSet.getString(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return categories;
	}

	public ArrayList<Question> retrieveQuestionsbyKeyword(String key)
			throws SQLException, ClassNotFoundException, NamingException {
		ArrayList<Question> questions = new ArrayList<Question>();
		int qid = 0;
		Question question = null;
		Boolean type = null;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("Q") && (key.contains("TF") || key.contains("MC"))) {
				qid = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			qid = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT a.* FROM tbl_question AS a INNER JOIN tbl_choice AS b ON a.question_id = b.question_id WHERE a.question_id = ? OR UPPER(a.question) LIKE UPPER(?) OR UPPER(a.question_category) LIKE UPPER(?) OR UPPER(b.answer) LIKE UPPER(?)";
			this.preparedStatement = this.connection.prepareStatement(select);

			this.preparedStatement.setInt(1, qid);
			this.preparedStatement.setString(2, intkey);
			this.preparedStatement.setString(3, intkey);
			this.preparedStatement.setString(4, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				type = this.resultSet.getBoolean(3);

				if (type) {
					question = new MultipleChoice();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "MC");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
					questions.add(question);
				} else {
					question = new TrueOrFalse();
					question.setQuestionID("Q" + this.resultSet.getInt(1)
							+ "TF");
					question.setQuestion(this.resultSet.getString(2));
					question.setCategory(this.resultSet.getString(4));
					questions.add(question);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return questions;
	}

	public boolean insertQuestion(Question question) throws SQLException,
			ClassNotFoundException, NamingException {
		boolean success = false;
		String insert = "INSERT INTO tbl_question(question,question_type,question_category) VALUES(?,?,?)";
		int row = 0;

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, question.getQuestion());

			if (question instanceof MultipleChoice) {
				this.preparedStatement.setBoolean(2, true);
			} else {
				this.preparedStatement.setBoolean(2, false);
			}
			this.preparedStatement.setString(3, question.getCategory());
			row += this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}
		return success;
	}

	public boolean updateQuestion(Question question) throws SQLException,
			ClassNotFoundException, NamingException {
		boolean success = false;
		String insert = "UPDATE tbl_question SET question=?,question_category=? WHERE question_id=?";
		int id = 0;
		int row = 0;

		try {
			id = Integer.parseInt(question.getQuestionID().replaceAll("[^\\d]",
					""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setString(1, question.getQuestion());
			this.preparedStatement.setString(2, question.getCategory());
			this.preparedStatement.setInt(3, id);
			row += this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean addQuestionToExam(String questionID, String examid)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		int id = 0;
		int eid = 0;
		String insert = "";
		int row = 0;

		try {
			id = Integer.parseInt(questionID.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			insert = "INSERT IGNORE INTO tbl_exam_questions(exam_id,question_id) VALUES((SELECT exam_id FROM tbl_exam WHERE exam_id=?),(SELECT question_id FROM tbl_question WHERE question_id=?))";
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setInt(1, eid);
			this.preparedStatement.setInt(2, id);
			row += this.preparedStatement.executeUpdate();

			this.connection.commit();

			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean removeAllQuestionsFromExam(String examid)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String insert = "";
		int eid = 0;
		int row = 0;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			insert = "DELETE FROM tbl_exam_questions WHERE exam_id=(SELECT exam_id FROM tbl_exam WHERE exam_id=?)";
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setInt(1, eid);
			row += this.preparedStatement.executeUpdate();
			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return success;
	}

	public int retrieveMaxQuestionID() throws ClassNotFoundException,
			SQLException, NamingException {
		int id = 0;

		try {
			this.connect();
			String select = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'beta_test_engine' AND TABLE_NAME = 'tbl_question'";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				id = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return id;
	}
}
